package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import domain.City;
import utils.JDBCUtils;

public class CityDAO {
	// 根据code查询城市
	public City findByCode(int countrycode) throws SQLException {
		// 1. 连接
		Connection conn = JDBCUtils.getConnection();
		// 2.
		String sql = "select * from city where CountryCode=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, countrycode);
		// 3.
		ResultSet rs = stmt.executeQuery();
		City c = null;

		if (rs.next()) {
			String name = rs.getString("Name");
			String id = rs.getString("ID");
			c = new City(countrycode,name,id);
		}

		return c;
	}

	// 查询所有
	public List<City> findAll() throws SQLException {
		// 1. 连接
		Connection conn = JDBCUtils.getConnection();
		// 2.
		String sql = "select * from city";
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3.
		ResultSet rs = stmt.executeQuery();
		List<City> list = new ArrayList<>();
		while (rs.next()) {
			int id = rs.getInt("ID");
			String name = rs.getString("Name");
			String countrycode = rs.getString("CountryCode");
			City c = new City(id, name, countrycode);
			list.add(c);
		}

		return list;
	}
}
